with summary_order as (--订单录入时间的汇总
    select project_name                                                                        --项目名称
         , date_time                                                                           --日期
         , customer_network_code
         , customer_network_name
         , customer_center_code
         , customer_center_name
         , customer_agent_code
         , customer_agent_name
         , count(1)                                                      as order_total        --订单总量
         , sum(if(cancel_flag = 0, 1, 0))                                as need_taking_num    --应揽收订单量
         , sum(if(cancel_flag = 1, 1, 0))                                as cancel_num         --取消量
         , sum(if(is_waybill_flag = 1, 1, 0))                            as actual_taking_num  --实际揽收量
         , sum(if(cancel_flag = 0 and is_waybill_flag = 0, 1, 0))        as no_taking_num      --未揽收量
         , sum(if(cancel_flag = 0 and add6h_timely_pick_falg = 1, 1, 0)) as timely_taking_num  --及时揽收量
         , round(sum(if(cancel_flag = 0 and add6h_timely_pick_falg = 1, 1, 0)) / sum(if(cancel_flag = 0, 1, 0)),
                 6)                                                      as timely_taking_rate --揽收及时率
         , sum(if(express_flag = 1, 1, 0))                               as express_num        --破损量
         , sum(if(arbitration_flag = 1, 1, 0))                           as arbitration_nun    --遗失量
         , ''                                                            as need_sign_num      --应签收量
         , ''                                                            as have_sign_num      --签收及时量
         , ''                                                            as no_sign_num        --未签收量
         , ''                                                            as have_sign_rate     --签收及时率
         , ''                                                            as m6_no_sign_num     --超6天未签收量
         , ''                                                            as m7_no_sign_num     --超7天未签收量
         , 1                                                             as type
    from jms_dm.dm_mng_cn_project_order_detail_dt
    where dt <= '{{ execution_date |  cst_ds }}'
      and dt >= date_add('{{ execution_date |  cst_ds }}', -30)
    group by project_name --项目名称
           , date_time    --日期
           , customer_network_code
           , customer_network_name
           , customer_center_code
           , customer_center_name
           , customer_agent_code
           , customer_agent_name
),
     summary_sign as (--揽收时间的汇总
         select project_name                                              --项目名称
              , pick_date_time                                            --日期(揽收时间加上时效天数的时间) +6小时
              , customer_network_code
              , customer_network_name
              , customer_center_code
              , customer_center_name
              , customer_agent_code
              , customer_agent_name
              , sum(if(platform_effect_falg = 1, 1, 0)) as need_sign_num  --应签收量
              , sum(if(platform_effect_falg = 1 and sign_falg = 1 and substr(sign_time, 1, 10) <= pick_date_time, 1,
                       0))                              as have_sign_num  --签收及时量
              , sum(if(platform_effect_falg = 1 and
                       (sign_falg = 0 or (sign_falg = 1 and substr(sign_time, 1, 10) > pick_date_time)), 1,
                       0))                              as no_sign_num    --未签收量
              , round(sum(if(platform_effect_falg = 1 and sign_falg = 1 and substr(sign_time, 1, 10) <= pick_date_time,
                             1, 0)) /
                      sum(if(platform_effect_falg = 1, 1, 0)),
                      6)                                as have_sign_rate --签收及时率
              , sum(if(platform_effect_falg = 1 and ((sign_falg = 0 and pick_date_time<=date_add('{{ execution_date |  cst_ds }}',-6)) or (sign_falg = 1 and
                                                                       substr(m6_addeff_pick_time, 1, 10) < substr(sign_time, 1, 10))),
                       1,
                       0))                              as m6_no_sign_num --超6天未签收量
              , sum(if(platform_effect_falg = 1  and ((sign_falg = 0 and pick_date_time<=date_add('{{ execution_date |  cst_ds }}',-7))or (sign_falg = 1 and
                                                                       substr(m7_addeff_pick_time, 1, 10) < substr(sign_time, 1, 10))),
                       1,
                       0))                              as m7_no_sign_num --超7天未签收量
              , 2                                       as type
         from jms_dm.dm_mng_cn_project_order_taking_detail_dt
         where dt <= '{{ execution_date |  cst_ds }}'
           and dt >= date_add('{{ execution_date |  cst_ds }}', -30)
         group by project_name   --项目名称
                , pick_date_time --日期(揽收时间加上时效天数的时间) +6小时
                , customer_network_code
                , customer_network_name
                , customer_center_code
                , customer_center_name
                , customer_agent_code
                , customer_agent_name
     )
insert
overwrite
table
jms_dm.dm_mng_cn_project_network_summary_dt
partition
(
dt
)

select project_name
     , date_time
     , customer_network_code
     , customer_network_name
     , customer_center_code
     , customer_center_name
     , customer_agent_code
     , customer_agent_name
     , max(order_total)        as order_total
     , max(need_taking_num)    as need_taking_num
     , max(cancel_num)         as cancel_num
     , max(actual_taking_num)  as actual_taking_num
     , max(no_taking_num)      as no_taking_num
     , max(timely_taking_num)  as timely_taking_num
     , max(timely_taking_rate) as timely_taking_rate
     , max(express_num)        as express_num
     , max(arbitration_nun)    as arbitration_nun
     , max(need_sign_num)      as need_sign_num
     , max(have_sign_num)      as have_sign_num
     , max(no_sign_num)        as no_sign_num
     , max(have_sign_rate)     as have_sign_rate
     , max(m6_no_sign_num)     as m6_no_sign_num
     , max(m7_no_sign_num)     as m7_no_sign_num
     , type
     , dt
from (
         select project_name                --项目名称
              , date_time                   --日期
              , customer_network_code
              , customer_network_name
              , customer_center_code
              , customer_center_name
              , customer_agent_code
              , customer_agent_name
              , order_total                 --订单总量
              , need_taking_num             --应揽收订单量
              , cancel_num                  --取消量
              , actual_taking_num           --实际揽收量
              , no_taking_num               --未揽收量
              , timely_taking_num           --及时揽收量
              , timely_taking_rate          --揽收及时率
              , express_num                 --破损量
              , arbitration_nun             --遗失量
              , ''        as need_sign_num  --应签收量
              , ''        as have_sign_num  --签收及时量
              , ''        as no_sign_num    --未签收量
              , ''        as have_sign_rate --签收及时率
              , ''        as m6_no_sign_num --超6天未签收量
              , ''        as m7_no_sign_num --超7天未签收量
              , date_time as dt             --日期
              , type
         from summary_order
         union all
         select project_name                         --项目名称
              , pick_date_time                       --日期(揽收时间加上时效天数的时间) +6小时
              , customer_network_code
              , customer_network_name
              , customer_center_code
              , customer_center_name
              , customer_agent_code
              , customer_agent_name
              , ''             as order_total        --订单总量
              , ''             as need_taking_num    --应揽收订单量
              , ''             as cancel_num         --取消量
              , ''             as actual_taking_num  --实际揽收量
              , ''             as no_taking_num      --未揽收量
              , ''             as timely_taking_num  --及时揽收量
              , ''             as timely_taking_rate --揽收及时率
              , ''             as express_num        --破损量
              , ''             as arbitration_nun    --遗失量
              , need_sign_num                        --应签收量
              , have_sign_num                        --签收及时量
              , no_sign_num                          --未签收量
              , have_sign_rate                       --签收及时率
              , m6_no_sign_num                       --超6天未签收量
              , m7_no_sign_num                       --超7天未签收量
              , pick_date_time as dt
              , type
         from summary_sign
     ) a
group by project_name --项目名称
       , date_time    --日期
       , customer_network_code
       , customer_network_name
       , customer_center_code
       , customer_center_name
       , customer_agent_code
       , customer_agent_name
       , dt
       , type
    distribute by dt
       , 1
;
